iT邦幫忙

2019 iT 邦幫忙鐵人賽

DAY 6
2

今天實際舉例會寫到SQL,必須先說明一下......

資料庫中的資料是成千上萬...上百萬筆,而不是如下僅10筆的sample data,往後小馬只要寫到程式碼,處理方式一定都是針對多筆資料的寫法,而不會有針對個案少筆的特殊寫法。
有些個人慣用寫法(例如逗號位置)和解說方便,請先暫時不要計較,因前半段都還不是在教怎麼寫SQL,目的只是在舉例解說名詞,故不會太深入描述內容和寫法。細解SQL的內容會在第20天左右才開始。



好的,讓我們延續昨日的【資料採礦(Data Mining) -1.定義】。

在我的購物網站裡,我有如下的乾淨完整資料(資料清洗完畢的):
https://ithelp.ithome.com.tw/upload/images/20181006/20111566aNicrybcAN.png

而我現在需要如下的資料,好讓我之後作分析:

  1. 買過筆電但沒買過防窺片的人
  2. 已經超過1個月沒有消費的人
  3. 只在網站消費過一次的人

不用我多說,上面三種資料,聰明如你,一定知道找出這樣的人之後要做什麼事情。
但是,原始資料並無法直接給出我這三種資料,我必須透過某些邏輯判斷:

  1. 必須找出買過筆電的人,以及找出沒有買過防窺片的人,做交集。
  2. 找出每個人最後一次的消費日期,看誰的最後消費日期距今超過1個月。
  3. 必須計算出每個人消費次數,找出次數只有1的人。

透過上述可知,每個人的每次消費,都是正確且重要的資料,但你並不需要呈現每一筆資料,卻又必須拿到並使用每一筆資料,才有辦法得到你想要的準備資料。

對行內人的悄悄話
如果是你,你會怎麼處理?
如果我說不寫任何join,而且只包一層from,咱們是同路人嗎?

以下就來實際操作看看,把問題1~3分別解出tag1_goal,tag2_goal,tag3_goal當作新的欄位,增加在原始資料背後:

select A.*,
CASE WHEN tag1_1 > 0 and tag1_2 = 0        THEN 'O' ELSE 'X' END tag1_goal,
CASE WHEN date(now()) - date(tag2_1) >= 30 THEN 'O' ELSE 'X' END tag2_goal,
CASE WHEN tag3_1 = 1                       THEN 'O' ELSE 'X' END tag3_goal1,
CASE WHEN tag3_2 = tag3_3                  THEN 'O' ELSE 'X' END tag3_goal2

from(
SELECT *,
SUM(CASE WHEN 商品名稱='筆記型電腦' THEN 1 ELSE 0 END) over (PARTITION by 客戶名稱) tag1_1,
SUM(CASE WHEN 商品名稱='螢幕防窺片' THEN 1 ELSE 0 END) over (PARTITION by 客戶名稱) tag1_2,
MAX(交易日期)                                         over (PARTITION by 客戶名稱) tag2_1,
SUM(1)                                               over (PARTITION by 客戶名稱) tag3_1,
MAX(交易日期)                                         over (PARTITION by 客戶名稱) tag3_2,
MIN(交易日期)                                         over (PARTITION by 客戶名稱) tag3_3
FROM "ithelp_case02"
) A

https://ithelp.ithome.com.tw/upload/images/20181006/20111566AOUz1TM8tp.png

  • 備註一下~今天的now() = 2018/10/6

接著只要再寫個where,所有答案就都出來了。

相較之下,第一題反而是最簡單沒爭議,但第二三題會有這樣的爭論:

超過1個月沒有消費的人
「1個月是要用月計算還是日計算?」月計算意思例如,6月最後一次來,7月都還不算,8月開始才算。
「若用日計算,一個月是多久?30天還31天?」
「剛好30天沒來,要算還是不算?」

只在網站消費過一次的人
「一次是指只買了一件商品,還是只繳費一次?」
「如果只來過一天,同一天買了好幾件商品,算一次還是多次?」
tag3_goal1只買過一件商品的人當作一次;
tag3_goal2只買過一天的人當作一次。

今天小馬先不急著結論,明天我們再舉一例!



資料採礦 番外篇2

釐清定義是最耗時的經過

非同行可能很難想像,在我過去工作的時日裡,向需求單位釐清【定義細節】,是工作中最繁冗的一環(沒有之一),我們會遇到很多如上的問題。

我舉個今早在麥當勞的例子,我前面那位客人點餐:

客人:「我要一個2號餐。」
店員:「好的,要什麼飲料?」
客人:「然後兩杯熱咖啡。」
店員:「(略遲疑)好的兩杯熱咖啡嗎?」
客人:「對。」
店員:「請問2號餐要搭配什麼飲料呢?」
客人:「熱咖啡啊~」
店員:「好…」
於是結帳,結帳過程也沒人發現有什麼問題。
直到餐點好,客人看著上來的餐點,疑惑的問:

「為什麼有3杯咖啡?」

我意思是,如果,店員不懂得問出這句話:
所以是2號餐的飲料是熱咖啡,再另外加點2杯熱咖啡,所以總共會有3杯熱咖啡嗎?
在我的標準,這就是店員的問題。

拜託不要戰我服務業很辛苦這樣太歪樓,
我主要在講的是,供給者在做需求確認的時候,
他明明有能力可以將需求釐清,
他卻沒有做的時候,那這就是供給者的問題。

當然,我們已經假定了客人在第一時間,
就是沒辦法主動講清楚自己要幾杯咖啡。

如果有客人可以講清楚,
那就拍手喊聲「阿彌陀佛謝天謝地」再跟客人give me five一下,
而不是去認為客人要講清楚是天經地義,就是這樣!

欸這不是酸文(真的嗎?),
供給者本來就會比需求者更知道,
自己能供給的有什麼,供給出來是怎樣子。

那該怎麼做呢?

不能這樣問

超過1個月的人?那剛好30天是要算還是不要算?

必須這樣問

如果這個人最後一次的購買日期是8/1,假設今天是8/31,那這個人算不算超過1個月沒有購買?

而且按次序必須問到這些時間點

最後一次購買是8/1,假設今天是8/30?
最後一次購買是8/1,假設今天是8/31?
最後一次購買是8/1,假設今天是9/1?
最後一次購買是8/1,假設今天是9/2?
最後一次購買是9/1,假設今天是9/30?
最後一次購買是9/1,假設今天是10/1?
最後一次購買是9/1,假設今天是10/2?
最後一次購買是8/15,假設今天是9/14?
最後一次購買是8/15,假設今天是9/15?
最後一次購買是8/15,假設今天是9/16?
最後一次購買是9/15,假設今天是10/14?
最後一次購買是9/15,假設今天是10/15?
最後一次購買是9/15,假設今天是10/16?

當需求單位把答案回答出來,
他自己才會赫然發現:啊,原來自己不是用30天去想!
縱使小馬在對面桌子底下已握拳握出血來。

如果需求單位在回答完矛盾的答案後,
還遲鈍到以為自己在用30天去想,
那...就把2月搬出來舉例吧!

然後最常遇到的結果就是:

「我再回去想想。」
「我回去問問主管。」

所以說啊!如果有客人能在第一時間說清楚自己要什麼,
是不是真的該拍手喊聲「阿彌陀佛謝天謝地」再跟他give me five呢?


上一篇
資料採礦(Data Mining) -1.定義
下一篇
資料採礦(Data Mining) -3.推薦商品
系列文
AI無法一步登天,讓我們先從專有名詞定義開始。31
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 則留言

0
阿展展展
iT邦好手 1 級 ‧ 2019-09-04 20:19:22

+9無線滑鼠是什麼0.0

我要留言

立即登入留言